package com.niit.bc.dao.impl;

import com.niit.bc.bean.BookBean;
import com.niit.bc.dao.BookDao;
import com.niit.bc.util.web.PageUtil;
import com.niit.bc.util.web.StorageDate;
import com.sun.rowset.CachedRowSetImpl;

import javax.sql.RowSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BookDaoImpl extends BaseDao implements BookDao {

    // 通过sql语句的不同查找图书的top10
    public RowSet findBookList(String sql) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        CachedRowSetImpl crsi = null;

        conn = this.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            crsi = new CachedRowSetImpl();
            crsi.populate(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeSource(rs, null, ps, conn);
        }
        return crsi;
    }

    // 主页面的每一类别的列表top8
    public RowSet findBookShowIndex(int typeId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        CachedRowSetImpl crsi = null;

        conn = this.getConnection();
        String sql = "SELECT bc_books.bookId,bc_books.bookName,bc_books.bookPrice,"
                + "\r\n"
                + "bc_books.bookAuthor,bc_books.bookNum,bc_books.bookPhoto,"
                + "\r\n"
                + "bc_books.childTypeId,bc_types.typeId"
                + "\r\n"
                + "FROM "
                + "\r\n"
                + "bc_books ,bc_types ,bc_childtypes"
                + "\r\n"
                + "WHERE"
                + "\r\n"
                + "bc_types.typeId =  bc_childtypes.typeId AND"
                + "\r\n"
                + "bc_books.childTypeId =  bc_childtypes.childTypeId AND"
                + "\r\n"
                + "bc_types.typeId =  ? AND"
                + "\r\n"
                + "bc_books.bookShowIndex =  '1'"
                + "\r\n"
                + "ORDER BY"
                + "\r\n"
                + "bc_books.bookStorageTime DESC"
                + "\r\n"
                + "LIMIT 0, 8";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, typeId);
            rs = ps.executeQuery();
            crsi = new CachedRowSetImpl();
            crsi.populate(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeSource(rs, null, ps, conn);
        }
        return crsi;
    }

    /**
     * 张翠然
     */
    /**
     * 通过图书编号查询图书详情
     */
    public RowSet findBookByBookId(int bookId) {
        // TODO Auto-generated method stub
        com.sun.rowset.CachedRowSetImpl result = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        if (conn != null) {
            String sql = "select a.* ,b.childTypeName,c.typeId,c.typeName from bc_books as a ,bc_childTypes  as b , bc_types as c  where a.childTypeId=b.childTypeId and b.typeId=c.typeId and  bookId=?";
            try {
                pst = conn.prepareStatement(sql);
                pst.setInt(1, bookId);
                rs = pst.executeQuery();
                result = new CachedRowSetImpl();
                result.populate(rs);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(rs, null, pst, conn);
            }
        }
        return result;
    }

    /**
     * 点击查看该图书详情时，更新该图书的点击量
     */
    public int updateBookHits(int bookHits, int bookId) {
        int row = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        conn = this.getConnection();
        bookHits = bookHits + 1;
        if (conn != null) {
            String sql = "update bc_books set bookHits=? where bookId=?";
            try {
                pst = conn.prepareStatement(sql);
                pst.setInt(1, bookHits);
                pst.setInt(2, bookId);
                row = pst.executeUpdate();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(null, null, pst, conn);
            }
        }
        return row;
    }


    /**
     * 通过页面信息查询符合条件的图书列表
     */
    public RowSet findBookListByInfo(PageUtil pageUtil, String SQL) {
        // TODO Auto-generated method stub
        com.sun.rowset.CachedRowSetImpl result = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        if (conn != null) {
            try {
                String sql = SQL;
                sql += " limit ?,?";
                pst = conn.prepareStatement(sql);
                pst.setInt(1, pageUtil.getStartCount());
                pst.setInt(2, pageUtil.getPageSize());
                rs = pst.executeQuery();
                result = new CachedRowSetImpl();
                result.populate(rs);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(rs, null, pst, conn);
            }

        }
        return result;
    }

    /**
     * 通过页面信息查询符合条件的图书列表
     */
    public RowSet findAllBookListByInfo(PageUtil pageUtil, String SQL) {
        // TODO Auto-generated method stub
        com.sun.rowset.CachedRowSetImpl result = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        if (conn != null) {
            try {
                String sql = SQL;
                sql += " limit ?,?";
                pst = conn.prepareStatement(sql);
                pst.setInt(1, pageUtil.getStartCount());
                pst.setInt(2, pageUtil.getPageSize());
                rs = pst.executeQuery();
                result = new CachedRowSetImpl();
                result.populate(rs);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(rs, null, pst, conn);
            }

        }
        return result;
    }

    /**
     * 统计符合条件的图书列表的记录数目
     */
    public int countBooks(String SQL) {
        // TODO Auto-generated method stub
        int rowCount = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        if (conn != null) {
            try {
                String sql = "select count(d.bookId) from ((" + SQL + ")as d)";
                pst = conn.prepareStatement(sql);
                rs = pst.executeQuery();
                if (rs != null && rs.next()) {
                    rowCount = rs.getInt(1);
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(rs, null, pst, conn);
            }

        }
        return rowCount;
    }

    // 通过关键字查询图书列表
    public RowSet findBookByKey(String key, PageUtil pageUtil) {
        // TODO Auto-generated method stub
        com.sun.rowset.CachedRowSetImpl result = null;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        if (conn != null) {
            try {
                String sql = "select * from bc_books where bookName like '%"
                        + key + "%' limit ?,?";
                pst = conn.prepareStatement(sql);
                pst.setInt(1, pageUtil.getStartCount());
                pst.setInt(2, pageUtil.getPageSize());
                rs = pst.executeQuery();
                result = new CachedRowSetImpl();
                result.populate(rs);
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(rs, null, pst, conn);
            }

        }
        return result;
    }

    /**
     * 统计符合关键字的图书列表的记录数目
     */
    public int countBooksByKey(String key) {
        // TODO Auto-generated method stub
        int rowCount = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        conn = this.getConnection();
        if (conn != null) {
            try {
                String sql = "select count(d.bookId) from ((select * from bc_books where bookName like '%"
                        + key + "%')as d)";
                pst = conn.prepareStatement(sql);
                rs = pst.executeQuery();
                if (rs != null && rs.next()) {
                    rowCount = rs.getInt(1);
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(rs, null, pst, conn);
            }

        }
        return rowCount;
    }

    public int addBooks(BookBean bookBean, int childTypeId) {
        // TODO Auto-generated method stub
        Connection conn = null;
        PreparedStatement ps = null;
        int n = 0;
        String sql = "insert into bc_books (bc_books.bookISBN,bc_books.bookName," +
                "bc_books.bookPrice,bc_books.bookAuthor,bc_books.bookNum,bc_books.bookPub," +
                "bc_books.bookPubTime,bc_books.bookPhoto,bc_books.bookDescribe,bc_books.bookPage," +
                "bc_books.bookSale,bc_books.bookHits,bc_books.bookStorageTime,bc_books.bookShowIndex," +
                "bc_books.bookOther,bc_books.childTypeId) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, bookBean.getBookIsbn());
            ps.setString(2, bookBean.getBookName());
            ps.setDouble(3, bookBean.getBookPrice());
            ps.setString(4, bookBean.getBookAuthor());
            ps.setInt(5, bookBean.getBookNum());
            ps.setString(6, bookBean.getBookPub());
            ps.setString(7, bookBean.getBookPubTime());
            ps.setString(8, bookBean.getBookPhoto());
            ps.setString(9, bookBean.getBookDescribe());
            ps.setInt(10, bookBean.getBookPage());
            ps.setInt(11, 1);
            ps.setInt(12, 1);
            ps.setString(13, StorageDate.formatDate());
            ps.setInt(14, bookBean.getBookShowIndex());
            ps.setString(15, bookBean.getBookOther());
            ps.setInt(16, childTypeId);
//System.out.print(ps);
            n = ps.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            this.closeSource(null, null, ps, conn);
        }
        return n;
    }

    /**
     * 更新图书的数量和销售量
     */
    public int updateBookSale(int bookId) {
        // TODO Auto-generated method stub
        int row = 0;
        Connection conn = null;
        PreparedStatement pst = null;
        conn = this.getConnection();
        if (conn != null) {
            String sql = "update bc_books set bookSale=(SELECT Sum(bc_items.itemBookNum) FROM bc_items " +
                    "WHERE bc_items.bookId = ? )  where bookId=?";
            try {
                pst = conn.prepareStatement(sql);

                pst.setInt(1, bookId);
                pst.setInt(2, bookId);
                row = pst.executeUpdate();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } finally {
                this.closeSource(null, null, pst, conn);
            }
        }
        return row;
    }

}
